/**
 * Copyright (C) 2018-2020
 * All rights reserved, Designed By www.yixiang.co
 * 注意：
 * 本软件为www.yixiang.co开发研制，未经购买不得使用
 * 购买后可获得全部源代码（禁止转卖、分享、上传到码云、github等开源平台）
 * 一经发现盗用、分享等行为，将追究法律责任，后果自负
 */
package co.yixiang.modules.user.service.mapper;

import co.yixiang.common.mapper.CoreMapper;
import co.yixiang.modules.user.domain.YxDistributorRelation;
import co.yixiang.modules.user.service.dto.BillDetail;
import co.yixiang.modules.user.service.dto.BrokerageDetailDto;
import co.yixiang.modules.user.service.dto.FansListDto;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author yhh
 * @date 2020-09-16
 */
@Repository
public interface YxDistributorRelationMapper extends CoreMapper<YxDistributorRelation> {

    @Select("select * from yx_distributor_relation  where uid=#{uid}")
    YxDistributorRelation getByUid(Long uid);

    @Select("SELECT COUNT(1) from yx_distributor_relation yr, yx_user yu where yr.uid = yu.uid and yr.p_uid= #{uid} and yu.logout_account = '0' ")
    Integer getSelectCount(Long uid);

    @Select("SELECT COUNT(1) from yx_distributor_relation yr, yx_user yu where yr.uid = yu.uid and yr.p_uid in (select yr1.uid from yx_distributor_relation yr1 where yr1.p_uid= #{uid}) and yu.logout_account = '0' ")
    Integer selectTwoNum(Long uid);

    @Select("select yr.uid from yx_distributor_relation yr, yx_user yu where yr.uid = yu.uid and yr.p_uid in (${us}) and yu.logout_account = '0' ")
    List<Long> getUList(@Param("us") String us);



    @Select(" SELECT yu.uid,yu.phone,IFNULL(ya.real_name,yu.nickname) as 'nickname', yu.highest_level as `level` , yu.create_time as createTime from yx_distributor_relation yr , yx_user yu LEFT JOIN yx_authentication ya on ya.uid = yu.uid" +
            " where yr.uid = yu.uid and  yr.p_uid = #{uid} and yu.logout_account = '0' order by yu.create_time asc ")
    IPage<FansListDto> fansList1(Page<FansListDto> pageModel,@Param("uid") Long uid);

    @Select(" SELECT yu.uid , yu.phone ,IFNULL(ya.real_name,yu.nickname) as 'nickname', yu.highest_level as `level` , yu.create_time as createTime from yx_distributor_relation yr , yx_user yu LEFT JOIN yx_authentication ya on ya.uid = yu.uid" +
            " where yr.uid = yu.uid and p_uid in (SELECT uid from yx_distributor_relation where p_uid = #{uid}) and yu.logout_account = '0' order by yu.create_time asc  ")
    IPage<FansListDto> fansList2(Page<FansListDto> pageModel,@Param("uid") Long uid);

//    @Select(" SELECT yub.pm , yub.number , yub.create_time , '1' as type  , yu.nickname from yx_user_bill yub, yx_user yu where yub.uid = #{uid} and yub.category = 'brokerage_price' and yub.`status` = 1  and yu.uid = yub.uid " +
//            " UNION " +
//            " SELECT  '1' as pm , yde.price , yde.create_time , '2' as type , yu.nickname from yx_distributor_earnings yde , yx_user yu where yde.uid = #{uid} and yde.`status` = 1 and yde.is_over = 1 and yu.uid = yde.d_uid " +
//            " ORDER BY create_time DESC ")
//    IPage<BillDetail> billDetails(Page<BillDetail> pageModel,@Param("uid") Long uid);

    @Select(" SELECT number  , mark as title , pm as pm , create_time as createTime from yx_user_bill where category = 'brokerage_price' and `status` = 1 and type = 'extract' and uid = #{uid} ORDER BY create_time DESC")
    IPage<BillDetail> billDetails(Page<BillDetail> pageModel,@Param("uid") Long uid);


    @Select(" <script> select yu.real_name as nickname , yde.oid , yde.create_time , yde.price from yx_distributor_earnings yde , " +
            "yx_authentication yu  " +
            " where yde.d_uid = yu.uid and yde.uid = #{uid} " +
            " <if test=\"type == 1 or type == 2 \">  and type = 1  and cate = #{type} </if>"+
            " <if test=\"type == 3 \"> and type = 3 and cate = #{type} </if> " +
            " order by yde.create_time desc</script>")
    IPage<BrokerageDetailDto> brokerageDetails(Page<BrokerageDetailDto> pageModel,@Param("uid")  Long uid ,@Param("type") Integer type);


    @Select(" <script> SELECT IFNULL(SUM(yde.price),0) from yx_distributor_earnings yde where  yde.uid = #{uid} </script>")
    BigDecimal brokeragePrice(@Param("uid") Long uid,@Param("type") Integer type);

    @Select(" SELECT IFNULL(SUM(price),0) from yx_distributor_earnings where uid = #{uid} and cate = #{cate} and TO_DAYS(create_time) = TO_DAYS(now()) ")
    BigDecimal getB2ByUid(@Param("uid") Long uid , @Param("cate") Integer cate);

    @Select(" SELECT IFNULL(SUM(price),0) from yx_distributor_earnings where uid = #{uid}  and TO_DAYS(create_time) = TO_DAYS(now()) ")
    BigDecimal getB3ByUid(@Param("uid") Long uid);


    @Select(" SELECT IFNULL(SUM(r1.price - IFNULL(r2.price,0) - IFNULL(r2.service_charge,0)),0)  as price from yx_auction_record r1 " +
            " LEFT JOIN yx_auction_record r2 on r1.up_id = r2.id  and r1.id != r2.id  and r1.id != r2.id  " +
            " where r1.user_id = #{uid} and r1.is_buy = 1  ")
    BigDecimal getB1ByUid(@Param("uid") Long uid);


    @Select(" SELECT IFNULL(SUM(r1.price - IFNULL(r2.price,0) - IFNULL(r2.service_charge,0)),0)  as price from yx_auction_record r1 " +
            " LEFT JOIN yx_auction_record r2 on r1.up_id = r2.id  and r1.id != r2.id  " +
            " where r1.user_id = #{uid} and r1.is_buy = 1  and TO_DAYS(r1.create_time) = TO_DAYS(now()) ")
    BigDecimal getB5ByUid(@Param("uid") Long uid);

    @Select(" SELECT r1.id as oid, (r1.price - IFNULL(r2.price,0) - IFNULL(r2.service_charge,0))  as price , r1.create_time from yx_auction_record r1 " +
            " LEFT JOIN yx_auction_record r2 on r1.up_id = r2.id and r1.id != r2.id " +
            " LEFT JOIN yx_auction_order o1 on o1.auction_record_id = r1.id " +
            " where r1.user_id = #{uid} and r1.is_buy = 1 and o1.`status` != 5  order by r1.create_time desc")
    IPage<BrokerageDetailDto> getEarningsList(Page<BrokerageDetailDto> pageModel,@Param("uid") Long uid);


    @Select(" SELECT IFNULL(SUM(price),0) from yx_distributor_earnings where uid = #{uid} and " +
            "TO_DAYS( create_time ) = TO_DAYS(date_sub(now(), interval 1 day)) ")
    BigDecimal getC3ByUid( @Param("uid") Long uid);


    @Select(" SELECT IFNULL(SUM(price),0) from yx_distributor_earnings where uid = #{uid} and cate = #{cate} " +
            "and TO_DAYS( create_time ) = TO_DAYS(date_sub(now(), interval 1 day)) ")
    BigDecimal getC2ByUid( @Param("uid") Long uid,  @Param("cate") Integer cate);


    @Select(" SELECT IFNULL(SUM(r1.price - IFNULL(r2.price,0) - IFNULL(r2.service_charge,0)),0)  as price from yx_auction_record r1 " +
            " LEFT JOIN yx_auction_record r2 on r1.up_id = r2.id and r1.id != r2.id " +
            " where r1.user_id = #{uid} and r1.is_buy = 1  and TO_DAYS( r1.create_time ) = TO_DAYS(date_sub(now(), interval 1 day)) ")
    BigDecimal getB6ByUid( @Param("uid") Long uid);
}
